package com.zhou.dao.impl;

import com.zhou.bean.Express;
import com.zhou.dao.ExpressDao;
import com.zhou.utils.DbUtils;

import javax.swing.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class ExpressDaoImpl extends DbUtils implements ExpressDao {

    private static final String sql_console="select " +
            "count(id) data1_size," +
            "count(to_days(intime)=to_days(now()) or null) data1_day," +
            "count(status = 0 or null) data2_size," +
            "count(to_days(intime)=to_days(now()) and status=0 or null) data2_day " +
            "from express";
    //用于查询数据库中的所有快递信息
    private static final String sql_findAll="select * from express";
    //用于查询数据库中的分页信息
    private static final String sql_findLimit="select * from express limit ?,?";
    //通过取件码查询快递信息
    private static final String sql_findByCode="select * from express where code=?";
    //通过快递单号查询快递信息
    private static final String sql_findByNumber="select * from express where number=?";
    //通过用户手机号查询快递信息
    private static final String sql_findByUserPhone="select * from express where userphone=?";
    //通过录入人手机号查询快递信息
    private static final String sql_findBySysPhone="select * from express where sysPhone=?";
    //录入快递
    private static final String sql_insert="insert into express (number,username,userphone,company,code,intime,status,sysPhone) values(?,?,?,?,?,?,?,?)";
    //快递修改
    private static final String sql_update="update express set number=?,username=?,company=?,status=? where id = ?";
    //快递取件操作（状态码改变）
    private static final String sql_updateStatus="update express set status = 1,outtime=now(),code=null where code = ?";
    //快递的删除
    private static final String sql_delete="delete from express where id=?";


    /**
     * 用于查询数据库中的全部快递总数加新增，待取件快递总数加新增
     *
     * @return [{size:总数,day:新增},{size:总数,day:新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        String sql = sql_console;
        ResultSet query = query(sql, null);
        List<Map<String,Integer>> map = new ArrayList<>();
        try {
            while (query.next()){
                int data1_size = query.getInt("data1_size");
                int data1_day = query.getInt("data1_day");
                int data2_size = query.getInt("data2_size");
                int data2_day = query.getInt("data2_day");
                Map map1 = new HashMap();
                map1.put("data1_size",data1_size);
                map1.put("data1_day",data1_day);
                Map map2 = new HashMap();
                map2.put("data2_size",data2_size);
                map2.put("data2_day",data2_day);
                map.add(map1);
                map.add(map2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return map;
    }

    /**
     * 用于查询所有快递
     *
     * @param limit     是否分页的标记，true表示查询所有快递
     * @param pageIndex 页数
     * @param pageSize  页面大小
     * @return 快递的集合
     */
    @Override
    public List<Express> findAll(boolean limit, int pageIndex, int pageSize) {
        List list =new ArrayList();
        List expressList = new ArrayList();
        String sql = null;
        if (limit == true){
            sql = sql_findLimit;
            list.add(pageIndex);
            list.add(pageSize);
        }else {
            sql = sql_findAll;
        }
        ResultSet query = query(sql, list);
        try {
            while (query.next()){
                Express express = new Express();
                express.setId(query.getInt("id"));
                express.setNumber(query.getString("number"));
                express.setUserPhone(query.getString("userphone"));
                express.setUsername(query.getString("username"));
                express.setCompany(query.getString("company"));
                express.setCode(query.getString("code"));
                express.setInTime(query.getTimestamp("intime"));
                express.setOutTime(query.getTimestamp("outtime"));
                express.setStatus(query.getInt("status"));
                express.setSysPhone(query.getString("sysphone"));
                expressList.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return expressList;
    }

    /**
     * 根据快递单号，查询快递信息
     *
     * @param number 单号
     * @return 查询快递信息，单号不存在时返回null
     */
    @Override
    public Express findByNumber(String number) {
        String sql = sql_findByNumber;
        List list = new ArrayList();
        Express express = new Express();
        list.add(number);
        ResultSet query = query(sql, list);
        try {
            while (query.next()){
                express.setId(query.getInt("id"));
                express.setUserPhone(query.getString("userphone"));
                express.setUsername(query.getString("username"));
                express.setCompany(query.getString("company"));
                express.setNumber(query.getString("number"));
                express.setCode(query.getString("code"));
                express.setInTime(query.getTimestamp("intime"));
                express.setOutTime(query.getTimestamp("outtime"));
                express.setStatus(query.getInt("status"));
                express.setSysPhone(query.getString("sysphone"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return express;
    }

    /**
     * 根据快递取件码，查询快递信息
     *
     * @param code 取件码
     * @return 查询快递信息，取件码不存在时返回null
     */
    @Override
    public Express findByCode(String code) {
        String sql = sql_findByCode;
        List list = new ArrayList();
        Express express = new Express();
        list.add(code);
        ResultSet query = query(sql, list);
        try {
            while (query.next()){
                express.setId(query.getInt("id"));
                express.setNumber(query.getString("number"));
                express.setUsername(query.getString("username"));
                express.setUserPhone(query.getString("userphone"));
                express.setCode(query.getString("code"));
                express.setCompany(query.getString("company"));
                express.setInTime(query.getTimestamp("intime"));
                express.setOutTime(query.getTimestamp("outtime"));
                express.setStatus(query.getInt("status"));
                express.setSysPhone(query.getString("sysphone"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return express;
    }

    /**
     * 根据用户手机号，查询所有快递信息
     *
     * @param userPhone 手机号码
     * @return 查询快递信息列表
     */
    @Override
    public List<Express> findByUserPhone(String userPhone) {
        List list =new ArrayList();
        list.add(userPhone);
        List expressList = new ArrayList();
        String sql = sql_findByUserPhone;
        ResultSet query = query(sql, list);
        try {
            while (query.next()){
                Express express = new Express();
                express.setId(query.getInt("id"));
                express.setUsername(query.getString("username"));
                express.setNumber(query.getString("number"));
                express.setUserPhone(query.getString("userphone"));
                express.setCompany(query.getString("company"));
                express.setCode(query.getString("code"));
                express.setInTime(query.getTimestamp("intime"));
                express.setOutTime(query.getTimestamp("outtime"));
                express.setStatus(query.getInt("status"));
                express.setSysPhone(query.getString("sysphone"));
                expressList.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return expressList;
    }

    /**
     * 根据录入人手机号，查询录入的所有记录
     *
     * @param sysPhone 单号
     * @return 查询快递信息列表
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        List list =new ArrayList();
        list.add(sysPhone);
        List expressList = new ArrayList();
        String sql = sql_findBySysPhone;
        ResultSet query = query(sql, list);
        try {
            while (query.next()){
                Express express = new Express();
                express.setId(query.getInt("id"));
                express.setNumber(query.getString("number"));
                express.setUsername(query.getString("username"));
                express.setUserPhone(query.getString("userphone"));
                express.setCompany(query.getString("company"));
                express.setCode(query.getString("code"));
                express.setInTime(query.getTimestamp("intime"));
                express.setOutTime(query.getTimestamp("outtime"));
                express.setStatus(query.getInt("status"));
                express.setSysPhone(query.getString("sysphone"));
                expressList.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return expressList;
    }

    /**
     * 快递的录入
     * number,username,userphone,company,code,intime,status,sysPhone
     * @param express 录入的快递对象
     * @return 录入的结果 true表示成功 false表示失败
     */
    @Override
    public boolean insert(Express express) {
        String sql  = sql_insert;
        List list = new ArrayList();
        list.add(express.getNumber());
        list.add(express.getUsername());
        list.add(express.getUserPhone());
        list.add(express.getCompany());
        list.add(express.getCode());
        list.add(new Date());
        list.add(0);
        list.add(express.getSysPhone());
        try {
            int update = update(sql, list);
            return update==0?false:true;
        } catch (Exception e) {
            //System.out.println(e.getMessage());
            if (e.getMessage().endsWith("'express.numberIn'")){
                //是因为快递单号出现了重复
                throw new RuntimeException("快递单号重复,添加失败");
            }else if (e.getMessage().endsWith("'express.codeIn'")){
                //是因为取件码出现了重复
                throw new RuntimeException("取件码重复，添加失败");
            }
        } finally {
            closeAll();
        }
        return false;
    }

    /**
     * 快递的修改
     *
     * @param id         要修改的快递id
     * @param newExpress 修改的快递对象 number=?,username=?,company=?,status=?
     * @return 录入的结果 true表示成功 false表示失败
     */
    @Override
    public boolean update(int id, Express newExpress) {
        String sql = sql_update;
        List list = new ArrayList();
        list.add(newExpress.getNumber());
        list.add(newExpress.getUsername());
        list.add(newExpress.getCompany());
        list.add(newExpress.getStatus());
        list.add(id);
        try {
            int update = update(sql, list);
            return update>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return false;
    }

    /**
     * 更改快递的状态为1，表示取件完成
     *
     * @param code 要修改的取件码
     * @return 修改的结果，true表示成功，false表示失败
     */
    @Override
    public boolean updateStatus(String code) {
        String sql = sql_updateStatus;
        List list =new ArrayList();
        list.add(code);
        try {
            int update = update(sql, list);
            return update>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return false;
    }

    /**
     * 根据id，删除单个快递信息
     *
     * @param id 要删除的快递id
     * @return 删除的结果，true表示成功，false表示失败
     */
    @Override
    public boolean delete(int id) {
        String sql = sql_delete;
        List list = new ArrayList();
        list.add(id);
        try {
            int update = update(sql, list);
            return update>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return false;
    }
}
